package com.huazhi.wzsnw.service.impl;

import com.alibaba.fastjson.JSON;
import com.huazhi.wzsnw.constant.CommonUser;
import com.huazhi.wzsnw.mapper.*;
import com.huazhi.wzsnw.model.*;
import com.huazhi.wzsnw.service.ImportExcelService;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class ImportExcelServiceImpl implements ImportExcelService {

    @Autowired
    private AlertMapper alertMapper;
    @Autowired
    private ShuiZhiMapper shuiZhiMapper;
    @Autowired
    private LinedMapper linedMapper;
    @Autowired
    private VillageMapper villageMapper;
    @Autowired
    private XzMapper xzMapper;

    @Override
    public void importExcel(OutputStream out, JSONObject result, String name, String[] head) {
        try {
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
            Sheet sheet = wb.createSheet(name);// 创建一个sheet
            JSONArray jsonArray = result.getJSONArray("data");
            //第一行
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);//表名
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length - 1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);

            CellStyle cellStyleHead1 = wb.createCellStyle();
            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 12);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead1.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead1.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderLeft(CellStyle.BORDER_THIN);
            //第四行
            Row row1 = sheet.createRow(1);
            for (int k = 0; k < head.length; k++) {
                row1.setHeightInPoints(14);// 设置行高
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row1.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStyleHead1);
            }

            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

            int beginRowNumber = 2;// 表体开始行
            String [] values = new String[jsonArray.size()];
            if(!jsonArray.isEmpty()){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //第几行
                    rowDetail.setHeightInPoints(16);// 设置行高
                    net.sf.json.JSONObject jsonObject = net.sf.json.JSONObject.fromObject(jsonArray.get(i));
                    Iterator<String> it = jsonObject.keys();
                    int j = 0;
                    while(it.hasNext()){
                        Cell cellbody0 = rowDetail.createCell(j);
                        String key = it.next();
                        String value = jsonObject.getString(key);
                        cellbody0.setCellValue(value);
                        cellbody0.setCellStyle(cellStyleBody);// 设置样式
                        j++;
                    }
                }
            }

            Row lastRow = sheet.createRow(jsonArray.size() + beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
            User user = CommonUser.getUser();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            lastCell.setCellValue("制表人："+user.getName()+"   制表时间："+simpleDateFormat.format(new Date()));
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size() + beginRowNumber,jsonArray.size() + beginRowNumber,0,head.length - 1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);
            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public JSONObject getDeviceFailure() {
        List<JSONObject> jsonObjectList = new ArrayList<>();
        JSONObject result = new JSONObject();
        Alert alert1 = new Alert();
        alert1.setCategory("type");
        List<Alert> alertList = alertMapper.getAlertListByClassify(alert1,null,null);
        int typenum = 1, sum = 0;
        String [] values = new String[alertList.size()];
        if(!alertList.isEmpty()){
            for (int i = 0; i < alertList.size(); i++){
                JSONObject jsonObject = new JSONObject();
                String type = alertList.get(i).getType();
                jsonObject.put("sort",i+1);
                jsonObject.put("type",alertList.get(i).getType());
                jsonObject.put("sum",alertList.get(i).getSum());
                jsonObjectList.add(jsonObject);
                if(i != 0 && !type.equals(values[i - 1])){
                    typenum++;
                }
                sum = sum + alertList.get(i).getSum();
                values[i] = type;
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("sort","合计");
            jsonObject.put("type",typenum);
            jsonObject.put("sum",sum);
            jsonObjectList.add(jsonObject);
        }
        result.put("data",jsonObjectList);
        result.put("count",jsonObjectList.size());
        result.put("msg","");
        result.put("code",0);
        return result;
    }

    @Override
    public JSONObject getWaterQuality() {
        List<JSONObject> jsonObjectList = new ArrayList<>();
        JSONObject result = new JSONObject();
        List<ShuiZhi> shuiZhiList = shuiZhiMapper.getListByCategory();
        int typenum = 1, sum = 0;
        String [] values = new String[shuiZhiList.size()];
        if(!shuiZhiList.isEmpty()){
            for (int i = 0; i < shuiZhiList.size(); i++){
                JSONObject jsonObject = new JSONObject();
                String cunname = shuiZhiList.get(i).getCunname();
                jsonObject.put("sort",i + 1);
                jsonObject.put("cunname", cunname);
                jsonObject.put("sum",shuiZhiList.get(i).getSum());
                jsonObjectList.add(jsonObject);
                if(i != 0 && !cunname.equals(values[i - 1])){
                    typenum++;
                }
                sum = sum + shuiZhiList.get(i).getSum();
                values[i] = cunname;
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("sort","合计");
            jsonObject.put("cunname",typenum);
            jsonObject.put("sum",sum);
            jsonObjectList.add(jsonObject);
        }
        result.put("data",jsonObjectList);
        result.put("count",jsonObjectList.size());
        result.put("msg","");
        result.put("code",0);
        return result;
    }

    @Override
    public JSONObject getDeviceOfLined() {
        List<JSONObject> jsonObjectList = new ArrayList<>();
        JSONObject result = new JSONObject();
        List<LinedTj> linedTjList = linedMapper.getDeviceOfLined();
        int typenum = 1, numSum = 0, zhenSum = 0,cwhSum =0, video = 0,videosum = 0;
        int index = 0,lastindex = 0;
        Map<String,List<LinedTj>> map= new HashMap<>();
        if(!linedTjList.isEmpty()){
           for (LinedTj linedTj :linedTjList){
            if(map.containsKey(linedTj.getZhen())){
                List<LinedTj> list = map.get(linedTj.getZhen());
                list.add(linedTj);
                 cwhSum +=1;
                 numSum += linedTj.getLinedSum();
                 videosum += linedTj.getUrlSum();
            }else {
               List<LinedTj> list = new ArrayList<>();
               list.add(linedTj);
               map.put(linedTj.getZhen(),list);
                zhenSum +=1;
                cwhSum +=1;
                numSum += linedTj.getLinedSum();
                videosum += linedTj.getUrlSum();
            }
           }
            for (String key : map.keySet()){
                List<LinedTj> list = map.get(key);
                for (int i =0;i<list.size();i++){
                    LinedTj linedTj1 = list.get(i);
                    index+=1;
                    if(i ==0){
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("sort",index);
                        jsonObject.put("zhen",linedTj1.getZhen());
                        jsonObject.put("cunweihui",linedTj1.getCwh());
                        jsonObject.put("sum",linedTj1.getLinedSum());
                        jsonObject.put("video",linedTj1.getUrlSum());
                        jsonObjectList.add(jsonObject);
                    }else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("sort",index);
                        jsonObject.put("zhen","");
                        jsonObject.put("cunweihui",linedTj1.getCwh());
                        jsonObject.put("sum",linedTj1.getLinedSum());
                        jsonObject.put("video",linedTj1.getUrlSum());
                        jsonObjectList.add(jsonObject);
                    }
                }
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("sort","合计");
            jsonObject.put("zhen",zhenSum);
            jsonObject.put("cunweihui",cwhSum);
            jsonObject.put("sum",numSum);
            jsonObject.put("video",videosum);
            jsonObjectList.add(jsonObject);
        }
        result.put("data",jsonObjectList);
        result.put("count",jsonObjectList.size());
        result.put("msg","");
        result.put("code",0);
        return result;
    }

    public  Boolean checkHasKey(String cwh,List<LinedTj> list){
        if(list.size()==0){
            return  false;
        }
        for (LinedTj linedTj : list){
           if (cwh.equals(linedTj.getCwh())){
              return  true;
           }
        }
        return  false;
    }

    @Override
    public JSONObject getPeasantNumberOfVillage() {
        List<JSONObject> jsonObjectList = new ArrayList<>();
        JSONObject result = new JSONObject();
        List<Village> villageList = villageMapper.getPeasantNumberOfVillage();
        int typenum = 1, sum = 0,zhenNum = 0,allHushu =0;
        List<String> zhenNames = new ArrayList<>();
        String [] values = new String[villageList.size()];
        int index = 0,lastindex = 0;
        if(!villageList.isEmpty()){
            for (int i = 0; i < villageList.size(); i++){
                JSONObject jsonObject = new JSONObject();
                String zhen = villageList.get(i).getZhen();
                Village village = villageList.get(i);
              if(!zhenNames.contains(zhen)){
                 zhenNum += 1;
                 zhenNames.add(zhen);
                 allHushu += village.getHushu();
                 index  += 1;
                    jsonObject.put("sort",index);
                    jsonObject.put("zhen",village.getZhen());
                    jsonObject.put("cunweihui",village.getCunweihui());
                    jsonObject.put("sum",village.getHushu());
                    jsonObjectList.add(jsonObject);
              }else {
                  allHushu += village.getHushu();
                  index  += 1;
                  jsonObject.put("sort",index);
                  jsonObject.put("zhen","");
                  jsonObject.put("cunweihui",village.getCunweihui());
                  jsonObject.put("sum",village.getHushu());
                  jsonObjectList.add(jsonObject);
              }
//                if(i == 0){
//                    jsonObject.put("sort",index);
//                    jsonObject.put("zhen",villageList.get(i).getZhen());
//                    jsonObject.put("cunweihui","");
//                    jsonObject.put("sum",0);
//                    jsonObjectList.add(jsonObject);
//                    JSONObject jsonObject1 = new JSONObject();
//                    jsonObject1.put("sort",index + 1);
//                    jsonObject1.put("zhen","");
//                    jsonObject.put("cunweihui",villageList.get(i).getCunweihui());
//                    jsonObject1.put("sum",villageList.get(i).getSum());
//                    jsonObjectList.add(jsonObject1);
//                    index  = index + 2;
//                }else {
//                    if(zhen.equals(values[i - 1])){
//                        jsonObject.put("sort",index++);
//                        jsonObject.put("zhen","");
//                        jsonObject.put("cunweihui",villageList.get(i).getCunweihui());
//                        jsonObject.put("sum",villageList.get(i).getSum());
//                        jsonObjectList.add(jsonObject);
//                    }else {
//                        jsonObjectList.get(lastindex).put("sum",sum);
//                        lastindex = index;
//                        jsonObject.put("sort",index);
//                        jsonObject.put("zhen",zhen);
//                        jsonObject.put("cunweihui","");
//                        jsonObject.put("sum",0);
//                        jsonObjectList.add(jsonObject);
//                        JSONObject jsonObject1 = new JSONObject();
//                        jsonObject1.put("sort",index + 1);
//                        jsonObject1.put("zhen","");
//                        jsonObject1.put("cunweihui",villageList.get(i).getCunweihui());
//                        jsonObject1.put("sum",villageList.get(i).getSum());
//                        jsonObjectList.add(jsonObject1);
//                        index = index + 2;
//                    }
//                }
//                if(i != 0 && !zhen.equals(values[i - 1])){
//                    typenum++;
//                }
//                sum = sum + villageList.get(i).getSum();
//                values[i] = zhen;
//                if(i == villageList.size() - 1){
//                    jsonObjectList.get(lastindex).put("sum",sum);
//                }
//                jsonObject.put("sort",i + 1);
//                jsonObject.put("zhen",villageList.get(i).getZhen());
//                jsonObject.put("cunweihui",villageList.get(i).getCunweihui());
//                jsonObject.put("sum",villageList.get(i).getSum());
//                jsonObjectList.add(jsonObject);
//                if(i != 0 && !zhen.equals(values[i - 1])){
//                    typenum++;
//                }
//                sum = sum + villageList.get(i).getSum();
//                values[i] = zhen;
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("sort","合计");
            jsonObject.put("zhen",zhenNum);
            jsonObject.put("cunweihui",villageList.size());
            jsonObject.put("sum",allHushu);
            jsonObjectList.add(jsonObject);
        }
        result.put("data",jsonObjectList);
        result.put("count",jsonObjectList.size());
        result.put("msg","");
        result.put("code",0);
        return result;
    }
}
